﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DAL
{
    public class EntityCategoryDAL : AbstractDAL<EntityCategory>
    {
        static readonly string INSERT = "insert into EntityCategory values(null,@EID,@CID);select last_insert_rowid() newid;";
        static readonly string UPDATE = "update EntityCategory set EID=@EID,CID=@CID where id=@ID";
        static readonly string DELETE = "delete  from EntityCategory where id=@ID";
        static readonly string DELETE_DeleteAllByEID = "delete  from EntityCategory where EID=@EID";
        static readonly string SELECT = "select * from  EntityCategory where id=@ID";
        static readonly string SELECT_ALL = "select * from  EntityCategory";
        static readonly string SELECT_ALL_BY_EID = "select a.*,b.ParentID,b.Name from  EntityCategory a left join Category b on a.CID=b.ID where EID=@EID";

        protected override string InsertCommand
        {
            get
            {
                return INSERT;
            }
        }

        protected override string UpdateCommand
        {
            get { return UPDATE; }
        }

        protected override string DeleteCommand
        {
            get { return DELETE; }
        }

        protected override string SelectCommand
        {
            get { return SELECT; }
        }

        protected override string SelectAllCommand
        {
            get { return SELECT_ALL; }
        }

        protected override DbParameter[] GetInsertParameter(EntityCategory model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@EID", model.EID), SqlHelper.CreateParameter("@CID", model.CID) };
        }

        protected override void SyncID(EntityCategory model, object scalar)
        {
            model.ID = (long)scalar;
        }

        protected override DbParameter[] GetUpdateParameter(EntityCategory model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@EID", model.EID), SqlHelper.CreateParameter("@CID", model.CID) };
        }

        protected override DbParameter[] GetDeleteParameter(EntityCategory model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID) };
        }

        protected override DbParameter[] GetSeleteParameter(long id)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", id) };
        }

        protected override EntityCategory GetModel(DbDataReader reader)
        {
            EntityCategory entity = new EntityCategory();
            entity.ID = (long)reader["ID"];
            entity.EID = (long)reader["EID"];
            entity.CID = (long)reader["CID"];
            return entity;
        }

        protected EntityCategoryView GetEntityCategoryViewModel(DbDataReader reader)
        {
            EntityCategoryView entity = new EntityCategoryView();
            entity.ID = (long)reader["ID"];
            entity.EID = (long)reader["EID"];
            entity.CID = (long)reader["CID"];
            entity.ParentID = (long)reader["ParentID"];
            entity.Name = (string)reader["Name"];
            return entity;
        }

        public void DeleteAllByEID(long kid)
        {
            DbCommand command = null;
            try
            {
                command = SqlHelper.GetCommand(DELETE_DeleteAllByEID);
                DbParameter parameters = SqlHelper.CreateParameter("@EID", kid);
                command.Parameters.Add(parameters);
                command.ExecuteNonQuery();
            }
            finally
            {
                SqlHelper.Close(command);
            }
        }

        public List<EntityCategoryView> GetAllByEID(long eid)
        {
            List<EntityCategoryView> list = new List<EntityCategoryView>();
            DbDataReader reader = null;
            DbCommand command = null;
            try
            {
                command = SqlHelper.GetCommand(SELECT_ALL_BY_EID);
                command.Parameters.Add(SqlHelper.CreateParameter("@EID", eid));
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetEntityCategoryViewModel(reader));
                }
            }
            finally
            {
                SqlHelper.Close(command, reader);
            }
            return list;
        }
    }
}
